mysql mssql| access| oracle| DB2| sqlite| MongoDB| redis| nosql| Postgre SQL| mariadb|

mysql

推荐列表 站点导航

当前位置：首页 > 数据库 > mysql >

MySQL死锁套路之唯一索引下批量插入顺序不一致

来源:网络整理  作者:网友投稿  发布时间:2020-12-28 09:53
这篇文章主要给大家介绍了关于MySQL死锁套路之唯一索引下批量插入顺序不一致的相关资料，文中通过示例代码介绍的...

死锁的本质是资源竞争，批量插入如果顺序不一致很容易导致死锁，我们来分析一下这个情况。为了方便演示，把批量插入改写为了多条 insert。

先来做几个小实验，简化的表结构如下

?

1

2

3

4

5

6

7

  

CREATE TABLE `t1` (

 `id` int(11) NOT NULL AUTO_INCREMENT,

 `a` varchar(5),

 `b` varchar(5),

 PRIMARY KEY (`id`),

 UNIQUE KEY `uk_name` (`a`,`b`)

);

  

实验1：

在记录不存在的情况下，两个同样顺序的批量 insert 同时执行，第二个会进行锁等待状态

t1 t2  
begin;   begin;      
insert ignore into t1(a, b)values("1", "1");       成功  
    insert ignore into t1(a, b)values("1", "1");   锁等待状态  

可以看到目前锁的状态

?

1

2

3

4

5

6

7

  

mysql> select * from information_schema.innodb_locks;

+-------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+

| lock_id  | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |

+-------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+

| 31AE:54:4:2 | 31AE  | S   | RECORD | `d1`.`t1` | `uk_name` |   54 |   4 |  2 | '1', '1' |

| 31AD:54:4:2 | 31AD  | X   | RECORD | `d1`.`t1` | `uk_name` |   54 |   4 |  2 | '1', '1' |

+-------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+

  

在我们执行事务t1的 insert 时，没有在任何锁的断点处出现，这跟 MySQL 插入的原理有关系

insert 加的是隐式锁。什么是隐式锁？隐式锁的意思就是没有锁

在 t1 插入记录时，是不加锁的。这个时候事务 t1 还未提交的情况下，事务 t2 尝试插入的时候，发现有这条记录，t2 尝试获取 S 锁，会判定记录上的事务 id 是否活跃，如果活跃的话，说明事务未结束，会帮 t1 把它的隐式锁提升为显式锁（ X 锁）

源码如下

MySQL死锁套路之唯一索引下批量插入顺序不一致

t2 获取S锁的结果:DB_LOCK_WAIT

MySQL死锁套路之唯一索引下批量插入顺序不一致

实验2：

批量插入顺序不一致的导致的死锁

t1 t2  
begin          
insert into t1(a, b)values("1", "1");       成功  
    insert into t1(a, b)values("2", "2");   成功  
insert into t1(a, b)values("2", "2");       t1 尝试获取 S 锁，把 t2 的隐式锁提升为显式 X 锁，进入 DB_LOCK_WAIT  
    insert into t1(a, b)values("1", "1");   t2 尝试获取 S 锁，把 t1 的隐式锁提升为显式 X 锁，产生死锁  

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

  

------------------------

LATEST DETECTED DEADLOCK

------------------------

181101 9:48:36

*** (1) TRANSACTION:

TRANSACTION 3309, ACTIVE 215 sec inserting

mysql tables in use 1, locked 1

LOCK WAIT 3 lock struct(s), heap size 376, 2 row lock(s), undo log entries 2

MySQL thread id 2, OS thread handle 0x70000a845000, query id 58 localhost root update

insert into t1(a, b)values("2", "2")

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 55 page no 4 n bits 72 index `uk_name` of table `d1`.`t1` trx id 3309 lock mode S waiting

Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0

 0: len 1; hex 32; asc 2;;

 1: len 1; hex 32; asc 2;;

 2: len 4; hex 80000002; asc  ;;

 

*** (2) TRANSACTION:

TRANSACTION 330A, ACTIVE 163 sec inserting

mysql tables in use 1, locked 1

3 lock struct(s), heap size 376, 2 row lock(s), undo log entries 2

MySQL thread id 3, OS thread handle 0x70000a888000, query id 59 localhost root update

insert into t1(a, b)values("1", "1")

*** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 55 page no 4 n bits 72 index `uk_name` of table `d1`.`t1` trx id 330A lock_mode X locks rec but not gap

Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0

 0: len 1; hex 32; asc 2;;

 1: len 1; hex 32; asc 2;;

 2: len 4; hex 80000002; asc  ;;

 

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 55 page no 4 n bits 72 index `uk_name` of table `d1`.`t1` trx id 330A lock mode S waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0

 0: len 1; hex 31; asc 1;;

 1: len 1; hex 31; asc 1;;

 2: len 4; hex 80000001; asc  ;;

 

*** WE ROLL BACK TRANSACTION (2)

  

怎么样解决这样的问题呢？

一个可行的办法是在应用层排序以后再插入

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对服务器之家的支持。

原文链接：https://juejin.im/post/5ce63f8ee51d45775d516ee9

相关热词：

本站内容来源于网络，如有侵权请与我们联系，我们会及时删除，我们深感抱歉！
注：本站所有信息仅供用于网络技术学习参考,学习中请遵循相关法律法规！

本文地址: https://v30.fanwenzhu.com/sql/mysql/10122.shtml

上一篇：MySQL找出未提交事务信息的
下一篇：解决Node.js mysql客户端不支
相关文章
热门TAG
win10 ecshop 主机 阿里云 解决 配置 C# C++ 解析 SQL语句 命令 Go语言 方法 CSS3 HTML5 CSS win7 MSSQL 服务器配置 IIS7.5 IIS7 IIS6 IIS CentOS 7 Linux oracle数据库 oracle phpcms discuz discuz教程
最新文章
这些文件如果在configure命 这些文件如果在configure命

时间：2021-01-22

说明在数据库崩溃时内存 说明在数据库崩溃时内存

时间：2021-01-22

破解极验(geetest)验证码 破解极验(geetest)验证码

时间：2021-01-22

今天这种代码阅读方法仍 今天这种代码阅读方法仍

时间：2021-01-22

count(*) as cnt from sakila.fi count(*) as cnt from sakila.fi

时间：2021-01-22

可能你注意到系统提示的 可能你注意到系统提示的

时间：2021-01-22

搭建环境与运行 搭建环境与运行

时间：2021-01-22

MySQL主从复制的常见拓扑 MySQL主从复制的常见拓扑

时间：2021-01-22

热门文章
MySQL的CRUD操作+使用视图 MySQL的CRUD操作+使用视图

时间：2021-01-10

NodeJs（2）和MySQL（windows下） NodeJs（2）和MySQL（windows下）

时间：2021-01-05

详解MySQL开启远程连接权限 详解MySQL开启远程连接权限

时间：2021-01-05

MySQL查询优化:LIMIT 1避免全表扫描提高查询 MySQL查询优化:LIMIT 1避免全表扫描提高查询

时间：2020-12-07

MySQL数据检索+查询+全文本搜索 MySQL数据检索+查询+全文本搜索

时间：2021-01-10

mysql安装图解 mysql图文安装教程(详细说明 mysql安装图解 mysql图文安装教程(详细说明

时间：2020-12-23

MySQL8新特性：降序索引详解 MySQL8新特性：降序索引详解

时间：2020-12-23

对于innodb存储引擎的表只能指定数据路径 对于innodb存储引擎的表只能指定数据路径

时间：2021-01-20

MySQL死锁套路之唯一索引下批量插入顺序 MySQL死锁套路之唯一索引下批量插入顺序

时间：2020-12-28

可以通过动作标识来引用 DROP TABLE IF EXI 可以通过动作标识来引用 DROP TABLE IF EXI

时间：2021-01-20

Copyright © www.juheyunku.com      关于 | 合作 | 声明 | 联系 | 更新 | 地图 | Tags

MySQL死锁套路之唯一索引下批量插入顺序不一致

2020-12-28　编辑：网友投稿

死锁的本质是资源竞争，批量插入如果顺序不一致很容易导致死锁，我们来分析一下这个情况。为了方便演示，把批量插入改写为了多条 insert。

先来做几个小实验，简化的表结构如下

?

1

2

3

4

5

6

7

  

CREATE TABLE `t1` (

 `id` int(11) NOT NULL AUTO_INCREMENT,

 `a` varchar(5),

 `b` varchar(5),

 PRIMARY KEY (`id`),

 UNIQUE KEY `uk_name` (`a`,`b`)

);

  

实验1：

在记录不存在的情况下，两个同样顺序的批量 insert 同时执行，第二个会进行锁等待状态

t1 t2  
begin;   begin;      
insert ignore into t1(a, b)values("1", "1");       成功  
    insert ignore into t1(a, b)values("1", "1");   锁等待状态  

可以看到目前锁的状态

?

1

2

3

4

5

6

7

  

mysql> select * from information_schema.innodb_locks;

+-------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+

| lock_id  | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |

+-------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+

| 31AE:54:4:2 | 31AE  | S   | RECORD | `d1`.`t1` | `uk_name` |   54 |   4 |  2 | '1', '1' |

| 31AD:54:4:2 | 31AD  | X   | RECORD | `d1`.`t1` | `uk_name` |   54 |   4 |  2 | '1', '1' |

+-------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+

  

在我们执行事务t1的 insert 时，没有在任何锁的断点处出现，这跟 MySQL 插入的原理有关系

insert 加的是隐式锁。什么是隐式锁？隐式锁的意思就是没有锁

在 t1 插入记录时，是不加锁的。这个时候事务 t1 还未提交的情况下，事务 t2 尝试插入的时候，发现有这条记录，t2 尝试获取 S 锁，会判定记录上的事务 id 是否活跃，如果活跃的话，说明事务未结束，会帮 t1 把它的隐式锁提升为显式锁（ X 锁）

源码如下

MySQL死锁套路之唯一索引下批量插入顺序不一致

t2 获取S锁的结果:DB_LOCK_WAIT

MySQL死锁套路之唯一索引下批量插入顺序不一致

实验2：

批量插入顺序不一致的导致的死锁

t1 t2  
begin          
insert into t1(a, b)values("1", "1");       成功  
    insert into t1(a, b)values("2", "2");   成功  
insert into t1(a, b)values("2", "2");       t1 尝试获取 S 锁，把 t2 的隐式锁提升为显式 X 锁，进入 DB_LOCK_WAIT  
    insert into t1(a, b)values("1", "1");   t2 尝试获取 S 锁，把 t1 的隐式锁提升为显式 X 锁，产生死锁  

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

  

------------------------

LATEST DETECTED DEADLOCK

------------------------

181101 9:48:36

*** (1) TRANSACTION:

TRANSACTION 3309, ACTIVE 215 sec inserting

mysql tables in use 1, locked 1

LOCK WAIT 3 lock struct(s), heap size 376, 2 row lock(s), undo log entries 2

MySQL thread id 2, OS thread handle 0x70000a845000, query id 58 localhost root update

insert into t1(a, b)values("2", "2")

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 55 page no 4 n bits 72 index `uk_name` of table `d1`.`t1` trx id 3309 lock mode S waiting

Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0

 0: len 1; hex 32; asc 2;;

 1: len 1; hex 32; asc 2;;

 2: len 4; hex 80000002; asc  ;;

 

*** (2) TRANSACTION:

TRANSACTION 330A, ACTIVE 163 sec inserting

mysql tables in use 1, locked 1

3 lock struct(s), heap size 376, 2 row lock(s), undo log entries 2

MySQL thread id 3, OS thread handle 0x70000a888000, query id 59 localhost root update

insert into t1(a, b)values("1", "1")

*** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 55 page no 4 n bits 72 index `uk_name` of table `d1`.`t1` trx id 330A lock_mode X locks rec but not gap

Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0

 0: len 1; hex 32; asc 2;;

 1: len 1; hex 32; asc 2;;

 2: len 4; hex 80000002; asc  ;;

 

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 55 page no 4 n bits 72 index `uk_name` of table `d1`.`t1` trx id 330A lock mode S waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0

 0: len 1; hex 31; asc 1;;

 1: len 1; hex 31; asc 1;;

 2: len 4; hex 80000001; asc  ;;

 

*** WE ROLL BACK TRANSACTION (2)

  

怎么样解决这样的问题呢？

一个可行的办法是在应用层排序以后再插入

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对服务器之家的支持。

原文链接：https://juejin.im/post/5ce63f8ee51d45775d516ee9

本站内容来源于网络，如有侵权请与我们联系，我们会及时删除，我们深感抱歉！
注：本站所有信息仅供学习参考！
本文地址为 https://v30.fanwenzhu.com/sql/mysql/10122.shtml

相关文章

风云图片

这些文件如果在configure命

这些文件如果在configure命

说明在数据库崩溃时内存

说明在数据库崩溃时内存

破解极验(geetest)验证码

破解极验(geetest)验证码

今天这种代码阅读方法仍

今天这种代码阅读方法仍

count(*) as cnt from sakila.fi

count(*) as cnt from sakila.fi

可能你注意到系统提示的

可能你注意到系统提示的

推荐阅读

返回mysql频道首页